-- create tables
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Region')
BEGIN
	CREATE TABLE [dbo].[Region](
		[id_Region] [varchar](10) COLLATE database_default NOT NULL,
		[id_Parent] [varchar](10) COLLATE database_default NULL,
		[Name] [nvarchar](200) COLLATE database_default NOT NULL,
		CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED ([id_Region] ASC)
	)

	ALTER TABLE [dbo].[Region]  WITH CHECK 
	ADD  CONSTRAINT [FK_Region_Region] FOREIGN KEY([id_Parent])
	REFERENCES [dbo].[Region] ([id_Region])

	ALTER TABLE [dbo].[Region] CHECK CONSTRAINT [FK_Region_Region]
END
GO

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ZoomLevel')
CREATE TABLE [dbo].[ZoomLevel](
	[id_ZoomLevel] [int] NOT NULL,
	[GoogleMinimalZoom] [int] NULL,
	[GoogleMaximalZoom] [int] NULL,
	[Tolerance] [decimal](8, 8) NULL,
	CONSTRAINT [PK_ZoomLevel] PRIMARY KEY CLUSTERED ([id_ZoomLevel] ASC)
)
GO

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Point')
BEGIN
	CREATE TABLE [dbo].[Point](
		[id_Region] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
		[id_ZoomLevel] [int] NOT NULL,
		[PolylineIndex] [int] NOT NULL,
		[PointIndex] [bigint] NOT NULL,
		[Longitude] [decimal](18, 13) NOT NULL,
		[Latitude] [decimal](18, 13) NOT NULL,
		CONSTRAINT [PK_Point] PRIMARY KEY CLUSTERED (
			[id_Region] ASC,
			[id_ZoomLevel] ASC,
			[PolylineIndex] ASC,
			[PointIndex] ASC
		)
	)

	ALTER TABLE [dbo].[Point]  WITH CHECK ADD  CONSTRAINT [FK_Point_Region] FOREIGN KEY([id_Region])
	REFERENCES [dbo].[Region] ([id_Region])

	ALTER TABLE [dbo].[Point] CHECK CONSTRAINT [FK_Point_Region]

	ALTER TABLE [dbo].[Point]  WITH CHECK ADD  CONSTRAINT [FK_Point_ZoomLevel] FOREIGN KEY([id_ZoomLevel])
	REFERENCES [dbo].[ZoomLevel] ([id_ZoomLevel])

	ALTER TABLE [dbo].[Point] CHECK CONSTRAINT [FK_Point_ZoomLevel]
END
GO

-- create (or regenerate) stored procedures
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GetRegions')
DROP PROC [dbo].[GetRegions]
GO

CREATE PROC [dbo].[GetRegions]
AS
SELECT id_Region, id_Parent, [Name] FROM [dbo].[Region]
GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ContainsRegionGeometry')
DROP PROC [dbo].[ContainsRegionGeometry]
GO

CREATE PROC [dbo].[ContainsRegionGeometry]
  @RegionId varchar(10),
  @ZoomLevelId int,
  @Result bit output
AS
SELECT @Result = 0
SELECT TOP 1 @Result = 1 FROM Point WHERE id_Region = @RegionId AND id_ZoomLevel = @ZoomLevelId
GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GetRegionGeometry')
DROP PROC [dbo].[GetRegionGeometry]
GO

CREATE PROC [dbo].[GetRegionGeometry]
  @RegionId varchar(10),
  @ZoomLevelId int
AS
SELECT 
  PolylineIndex, Longitude, Latitude
FROM [dbo].[Point]
WHERE id_Region = @RegionId AND id_ZoomLevel = @ZoomLevelId
ORDER BY PolylineIndex, PointIndex
GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ExportPoint')
DROP PROC [dbo].[ExportPoint]
GO

ALTER PROC [dbo].[ExportPoint]
AS
SELECT 
  *
FROM (
  SELECT DISTINCT
    1 AS Tag,
    NULL AS Parent,
    id_Region AS [Region!1!id_Region],
    id_ZoomLevel AS [Region!1!id_ZoomLevel],
    NULL AS [Polyline!2!PolylineIndex],
    NULL AS [P!3!lat],
    NULL AS [P!3!lng],
    NULL AS [P!3!Index!hide]
  FROM Point
  UNION
  SELECT DISTINCT
    2,
    1,
    id_Region,
    id_ZoomLevel,
    PolylineIndex,
    NULL,
    NULL,
    NULL
  FROM Point
  UNION
  SELECT
    3,
    2,
    id_Region,
    id_ZoomLevel,
    PolylineIndex,
    Latitude,
    Longitude,
    [Index]
  FROM Point
) AS SUBQUERY
ORDER BY 
  [Region!1!id_ZoomLevel], 
  [Region!1!id_Region],
  [Polyline!2!PolylineIndex],
  [P!3!Index!hide]
FOR XML EXPLICIT
GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ExportRegion')
DROP PROC [dbo].[ExportRegion]
GO

CREATE PROC [dbo].[ExportRegion]
AS
SELECT 
  id_Region, id_Parent, [Name], [Level]
FROM Region
FOR XML AUTO
GO
